import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class exp7 {
    public static void main(String[] args){
        Connection conn = null ;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<product> Product = new ArrayList<>();
        try{
            conn = Connect.getConnection();
            stmt = conn.createStatement();
            //stmt.execute("drop table Product");

            stmt.execute("CREATE TABLE Product\n" +
                    "(Product_Code CHAR(7), Description VARCHAR(40), Price DECIMAL(10, 2))");
            stmt.execute("INSERT INTO Product VALUES ('116-064', 'Toaster', 24.95)");
            stmt.execute("INSERT INTO Product VALUES ('257-535', 'Hair dryer', 29.95)");
            stmt.execute("INSERT INTO Product VALUES ('643-119', 'Car vacuum', 19.95)");
            stmt.execute("select * from Product");
            rs = stmt.getResultSet();// 这里不需要再传入SQL语句
            while(rs.next()){
                String Product_code = rs.getString(1);
                String Description = rs.getString(2);
                double price = rs.getDouble(3);
                Product.add(new product(Product_code,Description,price));
            }
            //stmt.execute("drop table Product");
        }catch (Exception e){
            e.printStackTrace();
        }
        Scanner input = new Scanner(System.in);
        System.out.println("请输入想要查找的商品的最低价格");
        double price = input.nextDouble();
        ListIterator<product> L1 = Product.listIterator();
        while(L1.hasNext()){
            product p = L1.next();
            if(p.getPrice()>price)
                System.out.println(p.toString());
        }
        System.out.println("请输入新的‘Toaster’的价格");
        price = input.nextDouble();
        Product.get(0).setPrice(price);
        ListIterator<product> L2 = Product.listIterator();
        System.out.println("新的Product数据列表为");
        while(L2.hasNext()){
            System.out.println(L2.next().toString());
        }
        try{
            stmt.execute("UPDATE Product SET Price="+price+" WHERE Description = 'toaster'");
            stmt.execute("drop table Product");
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
}
